/*
npm init
npm install request  cheerio mysql
 */

const config = {
    host: "localhost",
    user: "root",
    password: "admin",
    datebase: "pachong2",
    table: "news"
};

const request = require("request")
const cheerio = require("cheerio")
const mysql = require('mysql')
var item = 0;
var con = mysql.createConnection({
    host: config.host,
    user: config.user,
    password: config.password
});
con.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");
    con.query("CREATE DATABASE " + config.datebase, function(err, result) {
        if (err) throw err;
        console.log("Database created");
    });
    con.query("USE " + config.datebase, function(err, result) {
        if (err) throw err;
        console.log("Database USE");
    });
    var sql = "CREATE TABLE " + config.table + "(newsTitle VARCHAR(2000), newsTime VARCHAR(2000) ,newsUrl VARCHAR(2000) )";
    con.query(sql, function(err, result) {
        if (err) throw err;
        console.log("Table created");
    });
});


request('http://news.dgut.edu.cn/dgut/xydt/news_list.shtml', function(err, res) {
    if (err) {
        console.log('请求出错');
    } else {
        var $ = cheerio.load(res.body, { decodeEntities: false });
        $('.listList').children('ul').children('li').each(function() { //找到li元素对象然后通过each遍历
            var newsTitle = $(this).children('a').text(); //得到<a>标签的文字
            var newsTime = $(this).children('span').eq(1).text(); //得到第二个<span>标签的文字
            var newsUrl = "http://news.dgut.edu.cn" + $(this).children('a').attr('href'); //得到<a>标签的href的值

            con.query(`INSERT INTO ${config.table} (newsTitle, newsTime, newsUrl) VALUE('${newsTitle}', '${newsTime}','${newsUrl}')`, function(err, data) {
                if (err) {
                    console.log("数据库连接错误");
                }

            })
            item++;
            console.log("已爬取" + item + "条记录");
        });
    }
});